#json !/usr/bin/env python
#-*- coding:utf-8 -*-
from __future__ import division
import os
import sys
import datetime
reload(sys) 
sys.setdefaultencoding('utf8')
import time
import MySQLdb
import calendar
sys.path.insert(0,'/home/liuyue/svn/wan/')
sys.path.insert(0,'/opt/wanalytics/')
from Tools import connectMySQL




def TotalArea():
    _yesday = datetime.datetime.today() + datetime.timedelta(-1)
    _datetime = _yesday.strftime('%Y%m%d')
    _datesql = " and date = '%s' " % _datetime
    _datesql = " "
    #数据库连接
    try:
        db = connectMySQL.connMySQL("stat")
    except MySQLdb.Error, e:
        resultStr = '{"status": "failed", "code": "4001", "str": "connect database failed"}'
        return resultStr
    db.autocommit(True)
    c = db.cursor()
    
    nowmonth = time.strftime('%m',time.localtime(time.time()))

    cc = int(nowmonth)
    cf = []
    for i in xrange(cc,12):
        ch={}
        ch['year'] = 2011
        if len(str(i)) == 1:
            i = "%s%s" % ("0",str(i))
        ch['month'] = i
        cf.append(ch)

    for i in xrange(1,cc+1):
        ch={}
        ch['year'] = 2012
        if len(str(i)) == 1:
            i = "%s%s" % ("0",str(i))
        ch['month'] = i
        cf.append(ch)
    
    chh = []
    for i in cf[::-1]:
        chh.append(i)
    cf = chh
    
    ghenglie = ""
    for cfv in cf:
        ghenglie += u"<th>%s/%s</th>" % (cfv['year'],cfv['month'])

    
    
    endbdate = "%s%s01" % (cf[11]['year'],cf[11]['month'])
    zuihouyitian = calendar.monthrange(cf[0]['year'],int(cf[0]['month']))
    startbdate = "%s%s%s" % (cf[0]['year'],cf[0]['month'],zuihouyitian[1])
    
    
    os_array = ['-1','61','59','52']
    device_array = ['-1','1','2']
    for os in os_array:
        for device in device_array:
            
            if os == '-1':
                _os = '61,59,52'
            else:
                _os = os
            if device == '-1':
                _device = '1,2'
            else:
                _device = device
            
            
            
            
            #省市概览表头
            g_prov_table = u"<div class=\"csv-box-header\"><strong>省市占比年度概览</strong><div class=\"mnum\" style=\"color:#444\"><span><span style=\"display:none;\"><input onclick=\"getCSVData_save('citydayvideoTableX1');\" type=\"button\"  id=\"createcsv\" value=\"生成csv\" /></span><a href=\"javascript:getCSVData('citydayvideoTableX1');\" >下载</a></span><a href=\"javascript:getCSVData('citydayvideoTableX1');\"><img src=\"resources/images/csv.png\" style=\"vertical-align: middle;\" \></a></div></div>"
            
            g_prov_table += u"<div style='overflow:auto;height:200px;'><table class='new_silver_table' id='citydayvideoTableX1'  ><thead><tr><th onclick=\" tableSortingly('citydayvideoTableX1', 1,1); \"  id='citydayvideoTableX1th1' >序号</th><th>省市</th>"
            
            #城市概览表头
            g_city_table = u"<div class=\"csv-box-header\"><strong>城市占比年度概览</strong><div class=\"mnum\" style=\"color:#444\"><span><span style=\"display:none;\"><input onclick=\"getCSVData_save('citydayvideoTableX2');\" type=\"button\"  id=\"createcsv\" value=\"生成csv\" /></span><a href=\"javascript:getCSVData('citydayvideoTableX2');\" >下载</a></span><a href=\"javascript:getCSVData('citydayvideoTableX2');\"><img src=\"resources/images/csv.png\" style=\"vertical-align: middle;\" \></a></div></div>"
            
            g_city_table += u"<div style='overflow:auto;height:200px;'><table class='new_silver_table' id='citydayvideoTableX2'  ><thead><tr><th onclick=\" tableSortingly('citydayvideoTableX2', 1,1); \"  id='citydayvideoTableX2th1' >序号</th><th>城市</th>"
            
            #省市概览表开始
            g_prov_table += ghenglie
            g_city_table += ghenglie
            
                
            
            sql = " SELECT sum(a.uv),coalesce(b.prov_cn,'未知'),coalesce(b.city_cn,'未知'),date FROM `stat_day_total` a left join `stat_city_dict` b on a.prov = b.cprov_id and a.city = b.ccity_id WHERE  a.business_type = 2 and a.pid_stat = 1 and a.os_id in (%s) and a.device_type in (%s) and a.partner_mode in (1,2,3,8) and a.prov != 'ALL' and a.city != 'ALL' and a.ver = 'ALL' and date >= '%s' and date < '%s'  %s  group by coalesce(b.prov_cn,'未知'),coalesce(b.city_cn,'未知'),a.date order by sum(a.uv) desc " % (_os,_device,endbdate,startbdate,_datesql)
            print sql
            c.execute(sql)  
            result = c.fetchall()
            
            keyprovlist = []
            keycitylist = []                    
            for vv in result:
                if vv[1] not in keyprovlist:
                    keyprovlist.append(vv[1])
                if vv[2] not in keycitylist:
                    keycitylist.append(vv[2])
                
            
            kpl = []
            #keyprovlist = list(set(keyprovlist))
            #keycitylist = list(set(keycitylist))
            
            print keyprovlist
            for i in xrange(len(keyprovlist)):
                ck2 = []
                for j in xrange(len(cf)):
                    dk = []
                    for jjj in result:
                        ccdd = str(jjj[3])
                        p1 = "%s%s" % (cf[j]['year'],cf[j]['month'])
                        if jjj[1] == keyprovlist[i] and ccdd.find(p1) != -1:
                            dk.append(int(jjj[0]))
                        else:
                            dk.append(0)
                    ck2.append(sum(dk))
                kpl.append({'shulist':ck2})    
            
            print 'kpl done!'
            #print kpl
            jishu = 1
            for i in xrange(len(kpl)):
                bg = ""
                if jishu % 2 == 0:
                    bg = " class='new_silver_even' "
                else:
                    bg = " class='new_silver_odd' "
                g_prov_table += "<tr %s><td>%s</td><td>%s</td>" % (bg,jishu,keyprovlist[i])
                
                    
                for j in xrange(len(kpl[i]['shulist'])):
                    xian = kpl[i]['shulist'][j]
                    
                    zong = 0
                    
                    for kc in xrange(len(kpl)):
                        zong += int(kpl[kc]['shulist'][j])
                    
                    #print xian,zong
                    
                    #print xian/zong
                    
                    try:    
                        a = xian/zong
                    except:
                        a = 0
                    zhanbi = round(a,4)
                    
                    #print xian,zong,zhanbi
                    
                    nl = str(zhanbi).split(".")
                    if len(str(nl[1])) < 4:
                        g_prov_table += "<td>%s0%%</td>" % (zhanbi*100)
                    else:
                        g_prov_table += "<td>%s%%</td>" % (zhanbi*100) 
                
                g_prov_table += "</tr>"
                jishu = jishu + 1
            
            g_prov_table +="</table></div>"
            
            print 'prov...'
            
            #城市概览表开始
            
            
            
            
            #print startbdate
            
            kpl = []
            for i in xrange(len(keycitylist)):
                hangzong = []
                ck2 = []
                for j in xrange(len(cf)):
                    sk = []
                    dk = []
                    for jjj in result:
                        ccdd = str(jjj[3])
                        p1 = "%s%s" % (cf[j]['year'],cf[j]['month'])
                        if jjj[2] == keycitylist[i] and ccdd.find(p1) != -1:
                            sk.append(int(jjj[0]))
                            dk.append(int(jjj[0]))
                        else:
                            sk.append(0)
                            dk.append(0)
                    hangzong.append(sum(sk))
                    ck2.append(sum(dk))
                kpl.append({'zongshu':sum(hangzong),'shulist':ck2})    
            
            
            
            jishu = 1
            for i in xrange(len(kpl)):
                bg = ""
                if jishu % 2 == 0:
                    bg = " class='new_silver_even' "
                else:
                    bg = " class='new_silver_odd' "
                g_city_table += "<tr %s><td>%s</td><td>%s</td>" % (bg,jishu,keycitylist[i])
                
                    
                for j in xrange(len(kpl[i]['shulist'])):
                    xian = kpl[i]['shulist'][j]
           
                    zong = 0
                    
                    for kc in xrange(len(kpl)):
                        zong += int(kpl[kc]['shulist'][j])
                    
                    
                    try:    
                        a = xian/zong
                    except:
                        a = 0
                    
                    try:    
                        a = xian/zong
                    except:
                        a = 0
                    zhanbi = round(a,4)
                    
                    
                    
                    nl = str(zhanbi).split(".")
                    if len(str(nl[1])) < 4:
                        g_city_table += "<td>%s0%%</td>" % (zhanbi*100)
                    else:
                        g_city_table += "<td>%s%%</td>" % (zhanbi*100) 
                
                g_city_table += "</tr>"
                jishu = jishu + 1
            
            g_city_table +="</table></div>"
            
            print 'city...'
            
            g_table = "%s%s" % (g_prov_table,g_city_table)
            
            
            
            d = {'"':'\\"', "'":"\\'", "\0":"\\\0", "\\":"\\\\"}
            g_table = ''.join(d.get(c, c) for c in g_table)
            g_table = g_table.strip()
            
            
            sql = " insert into `result_total_yeartable` (`table`,`startdate`,`enddate`,`os_id`,`device_type`) values ('%s','%s','%s','%s','%s') " % (g_table,startbdate,endbdate,os,device)
            
            try:
                print c.execute(sql)
            except Exception,e:
                print e
            
            print 'done once again!'
      
    
    c.close()
    db.close()
    
    
def PaikeArea():
    _yesday = datetime.datetime.today() + datetime.timedelta(-1)
    _datetime = _yesday.strftime('%Y%m%d')
    _datesql = " and date = '%s' " % _datetime
    _datesql = " "
    #数据库连接
    try:
        db = connectMySQL.connMySQL("stat")
    except MySQLdb.Error, e:
        resultStr = '{"status": "failed", "code": "4001", "str": "connect database failed"}'
        return resultStr
    db.autocommit(True)
    c = db.cursor()
    
    nowmonth = time.strftime('%m',time.localtime(time.time()))

    cc = int(nowmonth)
    cf = []
    for i in xrange(cc,12):
        ch={}
        ch['year'] = 2011
        if len(str(i)) == 1:
            i = "%s%s" % ("0",str(i))
        ch['month'] = i
        cf.append(ch)

    for i in xrange(1,cc+1):
        ch={}
        ch['year'] = 2012
        if len(str(i)) == 1:
            i = "%s%s" % ("0",str(i))
        ch['month'] = i
        cf.append(ch)
    
    chh = []
    for i in cf[::-1]:
        chh.append(i)
    cf = chh
    
    ghenglie = ""
    for cfv in cf:
        ghenglie += u"<th>%s/%s</th>" % (cfv['year'],cfv['month'])

    
    
    
    os_array = ['-1','61','52']
    device_array = ['-1','1','2']
    for os in os_array:
        for device in device_array:
            
            if os == '-1':
                _os = '61,59,52'
            else:
                _os = os
            if device == '-1':
                _device = '1,2'
            else:
                _device = device
            
            
            
            #省市概览表头
            g_prov_table = "<div class=\"csv-box-header\"><strong>省市占比年度概览</strong><div class=\"mnum\" style=\"color:#444\"><span><span style=\"display:none;\"><input onclick=\"getCSVData_save('citydaypaikeTableX1');\" type=\"button\"  id=\"createcsv\" value=\"生成csv\" /></span><a href=\"javascript:getCSVData('citydaypaikeTableX1');\" >下载</a></span><a href=\"javascript:getCSVData('citydaypaikeTableX1');\"><img src=\"resources/images/csv.png\" style=\"vertical-align: middle;\" \></a></div></div>"
            
            g_prov_table += u"<div style='overflow:auto;height:200px;'><table class='new_silver_table' id='citydaypaikeTableX1'  ><thead><tr><th onclick=\" tableSortingly('citydaypaikeTableX1', 1,1); \"  id='citydaypaikeTableX1th1' >序号</th><th>省市</th>"
            
            #城市概览表头
            g_city_table = "<div class=\"csv-box-header\"><strong>城市占比年度概览</strong><div class=\"mnum\" style=\"color:#444\"><span><span style=\"display:none;\"><input onclick=\"getCSVData_save('citydaypaikeTableX2');\" type=\"button\"  id=\"createcsv\" value=\"生成csv\" /></span><a href=\"javascript:getCSVData('citydaypaikeTableX2');\" >下载</a></span><a href=\"javascript:getCSVData('citydaypaikeTableX2');\"><img src=\"resources/images/csv.png\" style=\"vertical-align: middle;\" \></a></div></div>"
            
            g_city_table += u"<div style='overflow:auto;height:200px;'><table class='new_silver_table' id='citydaypaikeTableX2'  ><thead><tr><th onclick=\" tableSortingly('citydaypaikeTableX2', 1,1); \"  id='citydaypaikeTableX2th1' >序号</th><th>城市</th>"
            
            #省市概览表开始
            g_prov_table += ghenglie
            endbdate = "%s%s01" % (cf[11]['year'],cf[11]['month'])
            zuihouyitian = calendar.monthrange(cf[0]['year'],int(cf[0]['month']))
            startbdate = "%s%s%s" % (cf[0]['year'],cf[0]['month'],zuihouyitian[1])
            
                
            
            sql = " SELECT sum(a.uv),coalesce(b.prov_cn,'未知'),coalesce(b.city_cn,'未知'),date FROM `stat_day_paike` a left join `stat_city_dict` b on a.prov = b.cprov_id and a.city = b.ccity_id WHERE  a.business_type = 4 and a.pid_stat = 1 and a.os_id in (%s) and a.device_type in (%s) and a.partner_mode in (1,2,3,8) and a.prov != 'ALL' and a.city != 'ALL' and a.ver = 'ALL' and date >= '%s' and date < '%s'  %s  group by coalesce(b.prov_cn,'未知'),coalesce(b.city_cn,'未知'),a.date order by sum(a.uv) desc " % (_os,_device,endbdate,startbdate,_datesql)
            print sql
            c.execute(sql)  
            result = c.fetchall()
            
            keyprovlist = []
            keycitylist = []                    
            for vv in result:
                if vv[1] not in keyprovlist:
                    keyprovlist.append(vv[1])
                if vv[2] not in keycitylist:
                    keycitylist.append(vv[2])
                
            
            kpl = []
            #keyprovlist = list(set(keyprovlist))
            #keycitylist = list(set(keycitylist))
            
            print keyprovlist
            for i in xrange(len(keyprovlist)):
                ck2 = []
                for j in xrange(len(cf)):
                    dk = []
                    for jjj in result:
                        ccdd = str(jjj[3])
                        p1 = "%s%s" % (cf[j]['year'],cf[j]['month'])
                        if jjj[1] == keyprovlist[i] and ccdd.find(p1) != -1:
                            dk.append(int(jjj[0]))
                        else:
                            dk.append(0)
                    ck2.append(sum(dk))
                kpl.append({'shulist':ck2})    
            
            print 'kpl done!'
            #print kpl
            jishu = 1
            for i in xrange(len(kpl)):
                bg = ""
                if jishu % 2 == 0:
                    bg = " class='new_silver_even' "
                else:
                    bg = " class='new_silver_odd' "
                g_prov_table += "<tr %s><td>%s</td><td>%s</td>" % (bg,jishu,keyprovlist[i])
                
                    
                for j in xrange(len(kpl[i]['shulist'])):
                    xian = kpl[i]['shulist'][j]
                    
                    zong = 0
                    
                    for kc in xrange(len(kpl)):
                        zong += int(kpl[kc]['shulist'][j])
                    
                    #print xian,zong
                    
                    #print xian/zong
                    
                    try:    
                        a = xian/zong
                    except:
                        a = 0
                    zhanbi = round(a,4)
                    
                    print xian,zong,zhanbi
                    
                    nl = str(zhanbi).split(".")
                    if len(str(nl[1])) < 4:
                        g_prov_table += "<td>%s0%%</td>" % (zhanbi*100)
                    else:
                        g_prov_table += "<td>%s%%</td>" % (zhanbi*100) 
                
                g_prov_table += "</tr>"
                jishu = jishu + 1
            
            g_prov_table +="</table></div>"
            
            print 'prov...'
            
            #城市概览表开始
            
            g_city_table += ghenglie
            
            
            #print startbdate
            
            kpl = []
            for i in xrange(len(keycitylist)):
                hangzong = []
                ck2 = []
                for j in xrange(len(cf)):
                    sk = []
                    dk = []
                    for jjj in result:
                        ccdd = str(jjj[3])
                        p1 = "%s%s" % (cf[j]['year'],cf[j]['month'])
                        if jjj[2] == keycitylist[i] and ccdd.find(p1) != -1:
                            sk.append(int(jjj[0]))
                            dk.append(int(jjj[0]))
                        else:
                            sk.append(0)
                            dk.append(0)
                    hangzong.append(sum(sk))
                    ck2.append(sum(dk))
                kpl.append({'zongshu':sum(hangzong),'shulist':ck2})    
            
            
            
            jishu = 1
            for i in xrange(len(kpl)):
                bg = ""
                if jishu % 2 == 0:
                    bg = " class='new_silver_even' "
                else:
                    bg = " class='new_silver_odd' "
                g_city_table += "<tr %s><td>%s</td><td>%s</td>" % (bg,jishu,keycitylist[i])
                
                    
                for j in xrange(len(kpl[i]['shulist'])):
                    xian = kpl[i]['shulist'][j]
           
                    zong = 0
                    
                    for kc in xrange(len(kpl)):
                        zong += int(kpl[kc]['shulist'][j])
                    
                    
                    try:    
                        a = xian/zong
                    except:
                        a = 0
                    
                    try:    
                        a = xian/zong
                    except:
                        a = 0
                    zhanbi = round(a,4)
                    
                    
                    
                    nl = str(zhanbi).split(".")
                    if len(str(nl[1])) < 4:
                        g_city_table += "<td>%s0%%</td>" % (zhanbi*100)
                    else:
                        g_city_table += "<td>%s%%</td>" % (zhanbi*100) 
                
                g_city_table += "</tr>"
                jishu = jishu + 1
            
            g_city_table +="</table></div>"
            
            print 'city...'
            
            g_table = "%s%s" % (g_prov_table,g_city_table)
            
            
            
            d = {'"':'\\"', "'":"\\'", "\0":"\\\0", "\\":"\\\\"}
            g_table = ''.join(d.get(c, c) for c in g_table)
            g_table = g_table.strip()
            
            
            sql = " insert into `result_paike_yeartable` (`table`,`startdate`,`enddate`,`os_id`,`device_type`) values ('%s','%s','%s','%s','%s') " % (g_table,startbdate,endbdate,os,device)
            
            try:
                print c.execute(sql)
            except Exception,e:
                print e
            
            print 'done once again!'
      
    
    c.close()
    db.close()
    

    





if __name__ == '__main__':
    cmd = ''
    if len(sys.argv) == 2:
        cmd = sys.argv[1]

    if cmd == 'totalarea':
        print "TotalArea()"
        TotalArea()
    elif cmd == 'paikearea':
        print "PaikeArea()"
        PaikeArea()
    else:
        print "Nothing Happen!"
